package com.sql;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import com.Model.Pojo.CreditCard;
import com.Model.Pojo.CunQvKuan;
import com.Model.Pojo.Invest;
import com.Model.Pojo.MoneyList;
import com.Model.Pojo.Users;

public class DAO {
	Connection con;
	PreparedStatement pt;
	ResultSet rs;
	public boolean login(String sql){ //登录
		boolean flag = false;
		con = Conn.getConnection();
		try{
			pt = con.prepareStatement(sql);
			rs = pt.executeQuery();
			if(rs.next()){
				flag = true;
			}
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			Conn.closeRs(rs);
			Conn.closePt(pt);
			Conn.closeCon(con);
		}return flag;
	}
	
	public ArrayList<Users> chaxun(String sql){ //查询users
		ArrayList<Users> list = new ArrayList<Users>();
		con = Conn.getConnection();
		try{
			pt = con.prepareStatement(sql);
			rs = pt.executeQuery();
			while(rs.next()){    //next（）获取里面的内容
				Users user = new Users();
			    user.setZhanghao(rs.getString(1));//getString（n）获取第n列的内容
			    user.setMima(rs.getString(2));
			    user.setYue(Integer.parseInt(rs.getString(3)));
			    user.setXingming(rs.getString(4));
			    user.setYouxiang(rs.getString(5));
			    user.setShoujihao(rs.getString(6));
			    user.setMoney(Integer.parseInt(rs.getString(7)));
			    list.add(user);//数据库中的列数是从1开始的
			}
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			Conn.closeRs(rs);
			Conn.closePt(pt);
			Conn.closeCon(con);
		}
		return list;
	}
	
	public ArrayList<CunQvKuan> chaxuncunqv(String sql){ //存取款查询
		ArrayList<CunQvKuan> list = new ArrayList<CunQvKuan>();
		con = Conn.getConnection();
		try{
			pt = con.prepareStatement(sql);
			rs = pt.executeQuery();
			while(rs.next()){    //next（）获取里面的内容
				CunQvKuan cq = new CunQvKuan();
			    cq.setId(rs.getString(1));//getString（n）获取第n列的内容
			    cq.setName(rs.getString(2));
			    cq.setMoney(Integer.parseInt(rs.getString(3)));
			    cq.setStyle(rs.getString(4));
			    cq.setDate(rs.getString(5));
			    cq.setYue(Integer.parseInt(rs.getString(6)));
			    list.add(cq);//数据库中的列数是从1开始的
			}
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			Conn.closeRs(rs);
			Conn.closePt(pt);
			Conn.closeCon(con);
		}
		return list;
	}
	
	public ArrayList<CreditCard> chaxunCreditCard(String sql){ //信用卡查询
		ArrayList<CreditCard> list = new ArrayList<CreditCard>();
		con = Conn.getConnection();
		try{
			pt = con.prepareStatement(sql);
			rs = pt.executeQuery();
			while(rs.next()){    //next（）获取里面的内容
				CreditCard creditcard = new CreditCard();
			    creditcard.setId(rs.getInt(1));//getString（n）获取第n列的内容
			    creditcard.setAccountNumber(rs.getString(2));
			    creditcard.setName(rs.getString(3));
			    creditcard.setType(rs.getString(4));
			    creditcard.setRealName(rs.getString(5));
			    creditcard.setIdNumber(rs.getString(6));
			    creditcard.setPass(rs.getString(7));
			    creditcard.setDate(rs.getString(8));
			    list.add(creditcard);//数据库中的列数是从1开始的
			}
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			Conn.closeRs(rs);
			Conn.closePt(pt);
			Conn.closeCon(con);
		}
		return list;
	}
	
	public ArrayList<MoneyList> chaxunMoneyList(String sql){ //账户记录查询
		ArrayList<MoneyList> list = new ArrayList<MoneyList>();
		con = Conn.getConnection();
		try{
			pt = con.prepareStatement(sql);
			rs = pt.executeQuery();
			while(rs.next()){    //next（）获取里面的内容
				MoneyList moneylist = new MoneyList();
			    moneylist.setId(rs.getInt(1));//getString（n）获取第n列的内容
			    moneylist.setName(rs.getString(2));
			    moneylist.setStyle(rs.getString(3));
			    moneylist.setValue(rs.getString(4));
			    moneylist.setDate(rs.getString(5));
			    moneylist.setCash(rs.getString(6));
			    moneylist.setSource(rs.getString(7));
			    list.add(moneylist);//数据库中的列数是从1开始的
			}
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			Conn.closeRs(rs);
			Conn.closePt(pt);
			Conn.closeCon(con);
		}
		return list;
	}
	
	public ArrayList<Invest> searchInvest(String sql){ //查询投资记录
		ArrayList<Invest> list = new ArrayList<Invest>();
		con = Conn.getConnection();
		try{
			pt = con.prepareStatement(sql);
			rs = pt.executeQuery();
			while(rs.next()){    //next（）获取里面的内容
				Invest user = new Invest();
				user.setId(rs.getInt(1));
			    user.setNumber(rs.getString(2));//getString（n）获取第n列的内容
			    user.setMoney(rs.getInt(3));
			    user.setMonth(Integer.parseInt(rs.getString(4)));
			    user.setAfterMoney(rs.getDouble(5));

			    list.add(user);//数据库中的列数是从1开始的
			}
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			Conn.closeRs(rs);
			Conn.closePt(pt);
			Conn.closeCon(con);
		}
		return list;
	}

	
	
	/**
     * 获取表中所有字段名称
     * @param tableName 表名
     * @return
     */
    public List<String> getColumnNames(String tableName) {//获取列名
        List<String> columnNames = new ArrayList<String>();
        //与数据库的连接
        con = Conn.getConnection();
        String tableSql = "SELECT * FROM " + tableName;
        try {
            pt = con.prepareStatement(tableSql);
            //结果集元数据
            ResultSetMetaData rsmd = pt.getMetaData();
            //表列数
            int size = rsmd.getColumnCount();
            for (int i = 0; i < size; i++) {
                columnNames.add(rsmd.getColumnName(i + 1));
            }
        } catch (SQLException e) {
        	e.printStackTrace();
        }finally{
			Conn.closeRs(rs);
			Conn.closePt(pt);
			Conn.closeCon(con);
		}
        return columnNames;
    }
	
	public void xiugai(String sql){ //修改
		con = Conn.getConnection();
		try{
			Statement stmt=con.createStatement();//创建一个Statement对象
			stmt.executeUpdate(sql);//执行SQL语句
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			Conn.closeRs(rs);
			Conn.closePt(pt);
			Conn.closeCon(con);
		}
	}
	
	public void zengjia(String sql){ //增加
		con = Conn.getConnection();
		try{
			Statement stmt=con.createStatement();//创建一个Statement对象
			stmt.executeUpdate(sql);//执行SQL语句
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			Conn.closeRs(rs);
			Conn.closePt(pt);
			Conn.closeCon(con);
		}
	}
}
